﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Common;
using System.Reflection;
using DevExpress.XtraCharts;
using CNCSoft.ErpApps.TMS.longcommon;

namespace CNCSoft.ErpApps.TMS
{
    public partial class frmMainDashBoard : Form
    {
        string _strSQL;
        private SqlConnection conn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);
        private DataSet ds = new DataSet();

        public frmMainDashBoard()
        {
            InitializeComponent();
        }

        private void frmMainDashBoard_Load(object sender, EventArgs e)
        {
            LoadData();
        }

        private void frmMainDashBoard_Enter(object sender, EventArgs e)
        {
            LoadData();
            AutoSetEndClass();
        }

        private void grvData_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.ColumnIndex == 1)
                {
                    string form_name = grvData.Rows[e.RowIndex].Cells[4].Value.ToString();
                    LongCommon.OpenForm(".longGiaoVu", String.Format(".{0}", form_name));
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #region coding
        private void LoadData()
        {
            try
            {
                DataTable tb = new DataTable("ReportTable");
                
                tb.Columns.Add("Description", typeof(string));
                tb.Columns.Add("Value", typeof(string));
                tb.Columns.Add("Symbol", typeof(string));
                tb.Columns.Add("Total", typeof(string));
                tb.Columns.Add("Link", typeof(string));

                this._strSQL = "SELECT * FROM MainDashboard";
                SqlDataAdapter da = new SqlDataAdapter(_strSQL, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "MainDashboard");
                conn.Open();

                foreach (DataRow r in ds.Tables["MainDashboard"].Rows)
                {
                    SqlCommand cmd;
                    if (r["Value"].ToString().Contains("@userid"))
                    {
                        string sql = r["Value"].ToString().Substring(0, r["Value"].ToString().Length - 7) + "'" 
                            + GlobalVariable.WSInfo.UserID + "'";
                        cmd = new SqlCommand(sql, conn);
                    }
                    else
                    {
                        cmd = new SqlCommand(r["Value"].ToString(), conn);
                    }
                    SqlCommand cmd2 = new SqlCommand(r["Total"].ToString(), conn);
                    DataRow row = tb.NewRow();
                    row["Value"] = cmd.ExecuteScalar().ToString();
                    if (r["Total"].ToString() != "")
                    {
                        row["Symbol"] = "/";
                        row["Total"] = cmd2.ExecuteScalar().ToString();
                    }
                    row["Description"] = r["Description"].ToString();
                    row["Link"] = r["Link"].ToString();
                    tb.Rows.Add(row);
                }
                conn.Close();
                grvData.DataSource = tb;
                grvData.Columns[0].DataPropertyName = "Description";
                grvData.Columns[0].Width = 250;
                grvData.Columns[1].DataPropertyName = "Value";
                grvData.Columns[1].Width = 40;
                grvData.Columns[2].Width = 20;
                grvData.Columns[3].Width = 40;
                grvData.Columns[4].Width = 5;
                grvData.Columns[4].Visible = false;
                grvData.MultiSelect = false;
                //---Chart---------------------------------
                int year = DateTime.Today.Year;
                LoadChart(year);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadEnquiryEveryMonthInYear(int year)
        {
            try
            {
                if (ds.Tables["TableEnquiryEveryMonthInYear"] != null)
                    ds.Tables["TableEnquiryEveryMonthInYear"].Clear();
                string sql = @"usp_EnquiryEveryMonthInYear @year = " + year;
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableEnquiryEveryMonthInYear");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadInvoiceEveryMonthInYear(int year)
        {
            try
            {
                if (ds.Tables["TableEveryMonthInYear"] != null)
                    ds.Tables["TableEveryMonthInYear"].Clear();
                string sql = @"usp_InvoiceEveryMonthInYear @year = " + year;
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableInvoiceEveryMonthInYear");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadOpenedClassEveryMonthInYear(int year)
        {
            try
            {
                if (ds.Tables["TableOpenedClassEveryMonthInYear"] != null)
                    ds.Tables["TableOpenedClassEveryMonthInYear"].Clear();
                string sql = @"usp_OpenedClassEveryMonthInYear @year = " + year;
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableOpenedClassEveryMonthInYear");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadChart(int year)
        {
            try
            {
                LoadEnquiryEveryMonthInYear(year);
                LoadInvoiceEveryMonthInYear(year);
                LoadOpenedClassEveryMonthInYear(year);
                //----------
                chartControl_Data.Series["Enquiry"].DataSource = ds.Tables["TableEnquiryEveryMonthInYear"];
                chartControl_Data.Series["Enrollment"].DataSource = ds.Tables["TableInvoiceEveryMonthInYear"];
                chartControl_Data.Series["OpenedClass"].DataSource = ds.Tables["TableOpenedClassEveryMonthInYear"];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        //Auto set End Class when Ngay ket thuc = today
        private void AutoSetEndClass()
        {
            try
            {
                string sql = @"update DM_KHOAHOC set TrangThai = 'End' where MaKhoaHoc in (select MaKhoaHoc from DM_KHOAHOC where NgayKetThuc = GETDATE());
                               update KhoaHoc_GiaoVien set DangDay = 0 where MaKhoaHoc in (select MaKhoaHoc from DM_KHOAHOC where NgayKetThuc = GETDATE())";
                SqlCommand cmd = new SqlCommand(sql, conn);
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #endregion

    }
}
